var sqlite3 = require('sqlite3').verbose();
var fs = require('fs');
var uuid = require('uuid');
var CreatDBSql = [
    // 选项表
    `CREATE TABLE option (
key         CHAR(50)    PRIMARY KEY     NOT NULL,
value       CHAR(50)                    NOT NULL
);`,
    // 用户表
    `CREATE TABLE user (
user_id     CHAR(36)    PRIMARY KEY     NOT NULL,
username    CHAR(16)                    NOT NULL,
password    CHAR(16)                    NOT NULL,
avater      CHAR(500)                   NOT NULL,
nickname    CHAR(8)                     NOT NULL,
disable     INT(2)                      NOT NULL,
update_time INT(14),
create_time INT(14)
);`,
    // 链接服务表
    `CREATE TABLE service (
service_id      CHAR(36)    PRIMARY KEY     NOT NULL,
title           CHAR(16)                    NOT NULL,
ip              CHAR(500)                   NOT NULL,
username        CHAR(16)                    NOT NULL,
password        CHAR(16)                    NOT NULL,
update_time     INT(14),
create_time     INT(14)
);`,
    // 项目表
    `CREATE TABLE project (
project_id  CHAR(36)    PRIMARY KEY     NOT NULL,
owner_user  CHAR(36)                    NOT NULL,
title       CHAR(16)                    NOT NULL,
description CHAR(500),
update_time INT(14),
create_time INT(14)
);`,
    // 文件夹表
    `CREATE TABLE folder (
folder_id             CHAR(36)      PRIMARY KEY     NOT NULL,
folder_project_id     CHAR(36)                      NOT NULL,
folder_parent_id      CHAR(36),
title                 CHAR(255)                     NOT NULL,
type                  CHAR(255)                     NOT NULL,
description           CHAR(1000),
folder_index_array    CHAR(10000),
child_index_array     CHAR(10000),
disable               INT(2)                        NOT NULL,
update_time           INT(14),
create_time           INT(14)
);`,
    // 项目成员表
    `CREATE TABLE project_user (
project_user_id     CHAR(36)      PRIMARY KEY   NOT NULL,
project_id          CHAR(36)                    NOT NULL,
user_id             CHAR(36)                    NOT NULL,
update_time         INT(14),
create_time         INT(14)
);`,
    // 项目URL表
    `CREATE TABLE project_url (
url_id              CHAR(36)      PRIMARY KEY   NOT NULL,
project_id          CHAR(36)                    NOT NULL,
str                 CHAR(500)                   NOT NULL,
title               CHAR(255)                   NOT NULL,
disable             INT(2)                      NOT NULL,
update_time         INT(14),
create_time         INT(14)
);`,
    // 项目model表
    `CREATE TABLE project_model (
model_id            CHAR(36)      PRIMARY KEY   NOT NULL,
project_id          CHAR(36)                    NOT NULL,
key                 CHAR(500)                   NOT NULL,
name                CHAR(255)                   NOT NULL,
text                CHAR(10000)                 NOT NULL,
update_time         INT(14),
create_time         INT(14)
);`,
    // 项目Api表
    `CREATE TABLE project_api (
api_id          CHAR(36)      PRIMARY KEY     NOT NULL,
project_id      CHAR(36)                      NOT NULL,
folder_id       CHAR(36)                      NOT NULL,
title           CHAR(225)                     NOT NULL,
type            CHAR(10)                      NOT NULL,
body_type       INT(2),
disable         INT(2),
status          INT(2),
description     CHAR(200),   
url             CHAR(255),
header          CHAR(3000),
body            CHAR(3000),
fromdata        CHAR(3000),
query           CHAR(3000),
rest            CHAR(3000),
before          CHAR(3000),
after           CHAR(3000),
update_time     INT(14),
create_time     INT(14)
);`,
    // 项目功能表
    `CREATE TABLE project_function (
function_id      CHAR(36)      PRIMARY KEY     NOT NULL,
project_id       CHAR(36)                      NOT NULL,
name             CHAR(225)                     NOT NULL,
key              CHAR(225)                     NOT NULL,
str              CHAR(10000)                   NOT NULL,
testStr          CHAR(255),
update_time      INT(14),
create_time      INT(14)
);`,
    // 项目缓存表
    `CREATE TABLE project_cache (
cache_id        CHAR(36)      PRIMARY KEY     NOT NULL,
project_id      CHAR(36)                      NOT NULL,
key             CHAR(225)                     NOT NULL,
name            CHAR(225)                     NOT NULL,
update_time     INT(14),
create_time     INT(14)
);`,
    // 项目存储本地缓存表
    `CREATE TABLE local_api_cache (
cache_id        CHAR(36)      PRIMARY KEY     NOT NULL,
project_id      CHAR(36)                      NOT NULL,
service_id      CHAR(36)                      NOT NULL,
key             CHAR(255)                     NOT NULL,
value           CHAR(255)                     NOT NULL,
update_time     INT(14),
create_time     INT(14)
);`,
    // 项目自定义Key值表
    `CREATE TABLE project_custom_key (
custom_key_id   CHAR(36)      PRIMARY KEY     NOT NULL,
project_id      CHAR(36)                      NOT NULL,
key             CHAR(255),
update_time     INT(14),
create_time     INT(14)
)`
];

var DB = {};

var createTable = (sql) => {
    return new Promise((resolve, rejected) => {
        DB.run(sql, function (err, res) {
            if (err) {
                rejected(err);
            } else {
                resolve(res);
            };
        });
    });
};
var creat = (sql) => {
    return new Promise((resolve, rejected) => {
        DB.run(sql, function (err, res) {
            if (err) {
                rejected(err);
            } else {
                resolve(res);
            };
        });
    });
};



var filePath = './test.db';
var initDB = async function (callback) {
    var hasFile = fs.existsSync(filePath);
    if (!hasFile) {
        console.log('Creat DB File');
        fs.openSync(filePath, 'w');
        DB = new sqlite3.Database(filePath, function (err) { });
        for (let i = 0; i < CreatDBSql.length; i++) {
            const element = CreatDBSql[i];
            await createTable(element);
        };
        // 创建版本号
        await creat(`INSERT INTO option values('version','3.0.0')`);

        // 创建默认皮肤设置
        await creat(`INSERT INTO option values('theme','theme-light')`);

        // 创建本地默认用户
        var creatLocalUser = `
        INSERT INTO user (user_id, username, password,  avater, nickname, disable, update_time, create_time) 
        VALUES ('Local', 'root','root', '001.png','Local', '0', ${new Date().getTime()}, ${new Date().getTime()});`;
        await creat(creatLocalUser);

        // 默认项目ID
        var defaultProjectId = uuid.v1();

        // 创建默认项目
        var defaultProject = `
        INSERT INTO project (project_id, owner_user, title, description, update_time, create_time) 
        VALUES('${defaultProjectId}','Local','测试项目', '这是一个测试项目', ${new Date().getTime()}, ${new Date().getTime()})`;
        await creat(defaultProject);

        // 创建默认项目人员
        var defaultProjectUser = `
        INSERT INTO project_user (project_user_id, project_id, user_id, update_time, create_time)
        VALUES('${uuid.v1()}','${defaultProjectId}','Local',${new Date().getTime()}, ${new Date().getTime()});`;
        await creat(defaultProjectUser);

        // 创建默认项目文件夹
        var defaultProjectFolder = `
        INSERT INTO folder (folder_id,folder_project_id,title,type,description,disable,update_time, create_time)
        VALUES('${uuid.v1()}','${defaultProjectId}','根目录',0,'根目录',0,${new Date().getTime()}, ${new Date().getTime()});`;
        await creat(defaultProjectFolder);


        
        callback(DB);
    } else {
        DB = new sqlite3.Database(filePath, function (err) { });
        callback(DB);
    }
};

module.exports = {
    init: initDB,
};
